package com.orange.modules.dao;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.orange.dbhelp.DbHelper;
import com.orange.dbhelp.page.Page;
import com.orange.modules.bean.SchoolContactsBean;
import com.orange.modules.entity.school.AuthSchoolContacts;

@Repository
public class AuthSchoolContactsRepository {
	
	@Resource(name = "dbHelper")
	private DbHelper dbHelper;
	
	@Autowired
	private AuthSchoolContactsDao authSchoolContactsDao;
	
	/**
	 * 查询联系人信息(根据id)
	 */
	public AuthSchoolContacts queryAuthSchoolContactsBypid(String pid) throws SQLException{
		AuthSchoolContacts as = authSchoolContactsDao.findOne(pid);
		return as;
	}
	
	
	
	
	/**
	 * 删除指定的联系人
	 */
	public void deleteAuthSchoolContactsBypid(String pid) throws SQLException{
		authSchoolContactsDao.delete(pid);
	}
	
	/**
	 * 增加指定学校的联系人信息
	 */
	public void insertAuthSchoolContacts(AuthSchoolContacts ac) throws SQLException{
		authSchoolContactsDao.save(ac);
		
	}
	
	/**
	 * 分页显示联系人信息
	 */
	public Page<AuthSchoolContacts> queryAuthSchoolContactsByPage(Integer pageNumber,Integer pageSize , Map<String ,String> map) throws SQLException{
		StringBuilder sql =  new StringBuilder("SELECT TOP 1000 a.p_id as pId,a.s_school_id as schoolId,a.s_name as name,a.s_type as type,"
				+"a.s_mobile_phone as mobilePhone,a.s_telephone as telephone,"
				+"a.s_email as email,a.s_create_time as createTime"
				+" FROM t_auth_school_contacts a WHERE 1 = 1");
		if (StringUtils.isNotBlank(map.get("pid"))) {
			sql.append(" AND a.p_id = '" + map.get("pid")+ "'");
		}
		if (StringUtils.isNotBlank(map.get("schoolId"))) {
			sql.append(" AND a.s_school_id = '" + map.get("schoolId")+ "'");
		}
		if (StringUtils.isNotBlank(map.get("type"))) {
			sql.append(" AND a.s_type = '" + map.get("type")+ "'");
		}
		Page<AuthSchoolContacts> page  = dbHelper.getPage(sql.toString(), AuthSchoolContacts.class, pageNumber, pageSize);
		return page;
		
	}
	
	/**
	 * 修改指定的联系人信息
	 */
	public void updateAuthSchoolContacts(AuthSchoolContacts ac) throws SQLException{
		authSchoolContactsDao.save(ac);
	}

	/**
	 * 根据学校id查询联系人信息
	 */
	public AuthSchoolContacts queryAuthSchoolContactsBySchoolId(String schoolId) throws SQLException{
		String sql = "SELECT a.p_id as pId,a.s_school_id as schoolId,a.s_name as name,"
				+ "a.s_type as type,a.s_mobile_phone as mobilePhone,a.s_telephone as telephone,"
				+ "a.s_email as email,a.s_create_time as createTime "
				+ "FROM t_auth_school_contacts a "
				+ "WHERE a.s_school_id = ?";
		AuthSchoolContacts as = dbHelper.getBean(sql, AuthSchoolContacts.class, schoolId);
		return as;
		
	}
	
	/**
	 * 根据type,查询联系人信息
	 */
	public Page<AuthSchoolContacts> queryAuthContactsBytype(Integer pageNumber,Integer pageSize , Map<String ,String> map) throws SQLException{
		String sql = "SELECT b.s_name , b.s_type , b.s_mobile_phone , b.s_telephone , b.s_email "
				+ "FROM t_auth_school a LEFT JOIN t_auth_school_contacts b "
				+ "ON a.p_id = b.s_school_id WHERE 1 = 1";
		if (StringUtils.isNotBlank(map.get("type"))) {
			sql += " AND b.s_type = '"+ map.get("type")+"'";
		}
		Page<AuthSchoolContacts> page  = dbHelper.getPage(sql.toString(), AuthSchoolContacts.class, pageNumber, pageSize);
		return page;
		
	}
	/**********************************************************************************************************/
	/**
	 * 联系人和学校
	 * @param pageNumber
	 * @param pageSize
	 * @param map
	 * @return
	 * @throws SQLException
	 */
	public Page<SchoolContactsBean> queryAuthSchoolContactsByPage1(Integer pageNumber,Integer pageSize , Map<String ,String> map) throws SQLException{
		StringBuilder sql =  new StringBuilder("SELECT t1.p_id as pid,t1.s_school_Id AS schoolId,t1.s_type as type,"
				+ "t1.s_name AS name,t1.s_mobile_Phone AS mobilePhone,"
				+ "t1.s_telephone AS telephone,t1.s_email AS email,"
				+ "t1.s_create_Time AS createTime,t2.s_major AS major,t2.s_school_Name AS schoolName "
				+ "FROM t_auth_school_contacts AS t1 LEFT JOIN t_auth_school AS t2 ON t1.s_school_id = t2.p_id");
		Page<SchoolContactsBean> page  = dbHelper.getPage(sql.toString(), SchoolContactsBean.class, pageNumber, pageSize);
		return page;
		
	}
	
	/**
	 * 分页显示联系人信息
	 */
	public Page<AuthSchoolContacts> queryAuthSchoolContactsByPage2(Integer pageNumber,Integer pageSize , String schoolId, String type) throws SQLException{
		StringBuilder sql =  new StringBuilder("SELECT TOP 1000 a.p_id as pId,a.s_school_id as schoolId,a.s_name as name,a.s_type as type,"
				+"a.s_mobile_phone as mobilePhone,a.s_telephone as telephone,"
				+"a.s_email as email,a.s_create_time as createTime"
				+" FROM t_auth_school_contacts a WHERE 1 = 1");
		if (StringUtils.isNotBlank(schoolId)) {
			sql.append(" AND a.s_school_id = '" +schoolId+ "'");
		}
		if (StringUtils.isNotBlank(type)) {
			sql.append(" AND a.s_type = '" + type+ "'");
		}
		Page<AuthSchoolContacts> page  = dbHelper.getPage(sql.toString(), AuthSchoolContacts.class, pageNumber, pageSize);
		return page;
		
	}
	
	/**
	 * 根据学校查询教科院负责人信息
	 * @throws SQLException 
	 */
	public List<AuthSchoolContacts> queryType1BySchool(String schoolId) throws SQLException{
		String sql = "select a.p_id as p_id from t_auth_school_contacts as a where a.s_school_id = ? and a.s_type = 1";
		return this.dbHelper.getBeanList(sql, AuthSchoolContacts.class, schoolId);
	}
	/**
	 * 根据学校查询教务处负责人信息
	 * @throws SQLException 
	 */
	public List<AuthSchoolContacts> queryType2BySchool(String schoolId) throws SQLException{
		String sql = "select a.p_id as p_id from t_auth_school_contacts as a where a.s_school_id = ? and a.s_type = 2";
		return this.dbHelper.getBeanList(sql, AuthSchoolContacts.class, schoolId);
	}
}

